﻿using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;

using Dapper;
using Labthink.PCSoft.UniversalHost4.Model;
using Labthink.PCSoft.UniversalHost4.Utility;
using FullData = Labthink.PCSoft.UniversalHost4.Model.FullData;

namespace Labthink.PCSoft.UniversalHost4.DataAccess
{
    public class SqliteDataAccess
    {
        private readonly string _file;

        public string LanguageColumnName { get; set; }

        public SqliteDataAccess(string file)
        {
            if (!File.Exists(file))
            {
                throw new FileNotFoundException($"can not find file {file}");
            }

            string locale = SystemInfo.GetCurrentLocale();
            LanguageColumnName = locale.ToUpper() == "ZH-CN" ? "ChineseText" : "EnglishText";
            _file = file;
        }

        public List<Param> GetParameters1()
        {
            List<Param> data;
            using var conn = new SQLiteConnection($"Data Source={_file};Version=3;");
            conn.Open();
            var x = conn.Query<ParameterInfo>(
                $@"select dataNo, columnName,unit,precision,{LanguageColumnName} as Text,dataTypeId,lan.languageId from tbl_paramInfo p 
                       inner join tbl_language lan on p.[LanguageId] = lan.[LanguageId]").ToList();
            data = x.Select(y =>
                new Param
                {
                    DataNo = y.DataNO,
                    ColumnName = y.ColumnName,
                    LanguageId = y.LanguageId,
                    Text = y.Text,
                    Unit = y.Unit,
                    DataType = (DataType)y.DataTypeId,
                    Precision = y.Precision,
                }
            ).ToList();

            return data;
        }

        public List<FullData> GetParameters()
        {
            List<FullData> data;
            using var conn = new SQLiteConnection($"Data Source={_file};Version=3;");
            conn.Open();
            var x = conn.Query<ParameterInfo>(
                $@"select dataNo, columnName,unit,precision,{LanguageColumnName} as Text,dataTypeId,lan.languageId from tbl_paramInfo p 
                       inner join tbl_language lan on p.[LanguageId] = lan.[LanguageId]").ToList();
            data = x.Select(y =>
                new FullData
                {
                    DataNo = y.DataNO,
                    ColumnName = y.ColumnName,
                    LanguageId = y.LanguageId,
                    Text = y.Text,
                    Unit = y.Unit,
                    DataType = (DataType)y.DataTypeId,
                    Precision = y.Precision,
                }
            ).ToList();

            return data;
        }

        public DataSet GetAllData()
        {
            var ds = new DataSet();
            using var conn = new SQLiteConnection($"Data Source={_file};Version=3;");
            conn.Open();
            var cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT * FROM tbl_param;SELECT * FROM tbl_realtimedata;SELECT * FROM tbl_result";
            using (var da = new SQLiteDataAdapter(cmd))
            {
                da.Fill(ds);
            }

            return ds;
        }

        public DataTable GetDtParams(string file)
        {
            var ds = new DataSet();
            using var conn = new SQLiteConnection($"Data Source={file};Version=3;");
            conn.Open();
            var cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT * FROM tbl_param;SELECT * FROM tbl_realtimedata;SELECT * FROM tbl_result";
            using (var da = new SQLiteDataAdapter(cmd))
            {
                da.Fill(ds);
            }

            return ds.Tables[0];
        }

        public DataTable GetDtResults(string file)
        {
            var ds = new DataSet();
            using var conn = new SQLiteConnection($"Data Source={file};Version=3;");
            conn.Open();
            var cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT * FROM tbl_result";
            using (var da = new SQLiteDataAdapter(cmd))
            {
                da.Fill(ds);
            }

            return ds.Tables[0];
        }

        public List<FullData> GetData()
        {
            List<FullData> data;
            using var conn = new SQLiteConnection($"Data Source={_file};Version=3;");
            conn.Open();
            var x = conn.Query<ParameterInfo>(
                $@"select dataNo, columnName,unit,precision,{LanguageColumnName} as Text,dataTypeId,lan.languageId from tbl_realtimeDataInfo p 
                       inner join tbl_language lan on p.[LanguageId] = lan.[LanguageId]").ToList();
            data = x.Select(y =>
                new FullData
                {
                    DataNo = y.DataNO,
                    ColumnName = y.ColumnName,
                    LanguageId = y.LanguageId,
                    Text = y.Text,
                    Unit = y.Unit,
                    DataType = (DataType)y.DataTypeId,
                    Precision = y.Precision,
                }
            ).ToList();

            return data;
        }

        public List<FullData> GetResults()
        {
            List<FullData> data;
            using var conn = new SQLiteConnection($"Data Source={_file};Version=3;");
            conn.Open();

            List<ParameterInfo> x = conn.Query<ParameterInfo>(
                $@"select dataNo, columnName,unit,precision,{LanguageColumnName} as Text,dataTypeId,lan.languageId from tbl_resultInfo p 
                       inner join tbl_language lan on p.[LanguageId] = lan.[LanguageId]").ToList();
            data = x.Select(y =>
                new FullData
                {
                    DataNo = y.DataNO,
                    ColumnName = y.ColumnName,
                    LanguageId = y.LanguageId,
                    Text = y.Text,
                    Unit = y.Unit,
                    DataType = (DataType)y.DataTypeId,
                    Precision = y.Precision,
                }
            ).ToList();

            return data;
        }

        public object GetOneParamValue(string name)
        {
            using var conn = new SQLiteConnection($"Data Source={_file};Version=3;");
            conn.Open();
            return conn.ExecuteScalar("SELECT " + name + " FROM tbl_param");
        }

        public int GetTestId()
        {
            using var conn = new SQLiteConnection($"Data Source={_file};Version=3;");
            conn.Open();
            return conn.ExecuteScalar<int>("SELECT TestId FROM tbl_overview");
        }

        public int GetTestId(string file)
        {
            using var conn = new SQLiteConnection($"Data Source={file};Version=3;");
            conn.Open();
            return conn.ExecuteScalar<int>("SELECT TestId FROM tbl_overview");
        }

        /// <summary>
        /// 获取所有试验类型名称（当前界面语言下）
        /// </summary>
        /// <returns></returns>
        public List<DbLanguage> GetTestTypes()
        {
            using var conn = new SQLiteConnection($"Data Source={_file};Version=3;");
            conn.Open();
            List<DbLanguage> tts = conn.Query<DbLanguage>(
                $"select LanguageId,{LanguageColumnName} as Text from tbl_language " +
                "where LanguageId like 'testtype%'").ToList();

            return tts;
        }

        public List<DbLanguage> GetMethods()
        {
            List<DbLanguage> tts;
            using (var conn = new SQLiteConnection($"Data Source={_file};Version=3;"))
            {
                conn.Open();
                tts = conn.Query<DbLanguage>(
                    $"select LanguageId,{LanguageColumnName} as Text from tbl_language " +
                    "where LanguageId like 'TestMethod%'").ToList();
            }

            return tts;
        }

        public List<DbLanguage> GetLanguages()
        {
            List<DbLanguage> languages;
            using var conn = new SQLiteConnection($"Data Source={_file};Version=3;");
            conn.Open();
            languages = conn.Query<DbLanguage>($"SELECT LanguageId,{LanguageColumnName} as Text FROM TBL_Language").ToList();

            return languages;
        }
    }
}
